Re: [GENERAL] Auto Ordering

At 20:41 +0200 on 27/10/1999, Stuart Rison wrote:


> In the example you give, you could do the changes with two UPDATE
> commands:
>
> 1) UPDATE questions SET order=0 WHERE order=5;
> 2) UPDATE questions SET order=order+1 WHERE order<5;
>
> It becomes more tricky when you try and move a question to a position
> other than the first one (e.g. question #6 to move to position #3 and all
> other questions to be shifted accordingly).
>
> This would take three UPDATEs:
>
> 1) UPDATE questions SET order=0 WHERE order=6;
> 2) UPDATE questions SET order=order+1 WHERE order>=3 and order<6;
> 3) UPDATE questions SET order=3 WHERE order=0;

Here is an alternative method of thinking which I used in the past - it
depends on other factors whether this is good or not. If only the order of
the questions is important, and not the actual number, then you can use
fractions. You can use a floating point field, or a fixed point (numeric)
one, or just an int field that normally gets the numbers 100, 200, 300.

Changing order then becomes very easy:

  UPDATE questions SET the_order=50 WHERE the_order=600;

Will change questions 100,200,300,400,500,600,700
To 50,100,200,300,400,500,700.

From time to time, though, you will have to renumber your questions, to
make sure you don't run out of fraction precision. You can do that with
something like:

SELECT the_order
INTO TABLE temp_numbers
FROM questions
ORDER BY the_order;

CREATE SEQUENCE new_seq INCREMENT 100 START 100;

UPDATE questions
SET the_order = nextval( 'new_seq' )
WHERE questions.the_order = temp_numbers.the_order;

DROP SEQUENCE new_seq;
DROP TABLE temp_numbers;

The idea is to do the renumbering in batch, and have a small penalty in
"real time".

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma